use musicman
go

IF NOT EXISTS (select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'UserFeedback')
BEGIN
CREATE TABLE [dbo].[UserFeedback](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Personid] [bigint] NOT NULL,
	[Subject] [varchar](100) NULL,	
	[Feedback] [varchar](1024) NOT NULL,
 CONSTRAINT [PK_UserFeedback] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[UserFeedback]  WITH NOCHECK ADD  CONSTRAINT [FK_UserFeedback_Users] FOREIGN KEY([Personid])
REFERENCES [dbo].[Users] ([Personid])
ALTER TABLE [dbo].[UserFeedback] CHECK CONSTRAINT [FK_UserFeedback_Users]
END
GO
 
IF NOT EXISTS (select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'UserStats')
BEGIN
CREATE TABLE [dbo].[UserStats](
	[Personid] [bigint] NOT NULL,
	[NUMLOGSWEB] [int] NOT NULL,	
	[NUMLOGSCLIENT] [int] NOT NULL,
 CONSTRAINT [PK_UserStats] PRIMARY KEY CLUSTERED 
(
	[Personid] ASC
) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[UserStats]  WITH NOCHECK ADD  CONSTRAINT [FK_UserStats_Users] FOREIGN KEY([Personid])
REFERENCES [dbo].[Users] ([Personid])
ALTER TABLE [dbo].[UserStats] CHECK CONSTRAINT [FK_UserStats_Users]
END
GO


-- ALTER TABLE SEARCH FILE

IF NOT EXISTS(SELECT * FROM MUSICMAN.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Length' AND TABLE_NAME ='SEARCHFILE' AND TABLE_SCHEMA = 'DBO')
BEGIN
 ALTER TABLE [MUSICMAN].[DBO].[SEARCHFILE] ADD [Length] bigint NOT NULL DEFAULT 0
END
GO
 
 
-- ALTER TABLE USERFEEDBACK
IF NOT EXISTS(SELECT * FROM MUSICMAN.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'DATE' AND TABLE_NAME ='USERFEEDBACK' AND TABLE_SCHEMA = 'DBO')
BEGIN
 ALTER TABLE [MUSICMAN].[DBO].[USERFEEDBACK] ADD [Date] DateTime NOT NULL DEFAULT GetDate()
END
GO

-- ALTER TABLE USERSTATS
IF NOT EXISTS(SELECT * FROM MUSICMAN.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'LASTLOGGEDDATE' AND TABLE_NAME ='USERSTATS' AND TABLE_SCHEMA = 'DBO')
BEGIN
 ALTER TABLE [MUSICMAN].[DBO].[USERSTATS] ADD [LastLoggedDate] DateTime NOT NULL DEFAULT GetDate()
END
GO

-- ALTER TABLE USERSTATS
IF NOT EXISTS(SELECT * FROM MUSICMAN.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'TOTALFILES' AND TABLE_NAME ='USERSTATS' AND TABLE_SCHEMA = 'DBO')
BEGIN
 ALTER TABLE [MUSICMAN].[DBO].[USERSTATS] ADD [TOTALFILES] bigint NOT NULL DEFAULT 0
END
GO

-- ALTER TABLE USERSTATS
IF NOT EXISTS(SELECT * FROM MUSICMAN.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'TOTALLENGTH' AND TABLE_NAME ='USERSTATS' AND TABLE_SCHEMA = 'DBO')
BEGIN
 ALTER TABLE [MUSICMAN].[DBO].[USERSTATS] ADD [TOTALLENGTH] bigint NOT NULL DEFAULT 0
END
GO

-- ALTER TABLE SEARCHJOB
IF NOT EXISTS(SELECT * FROM MUSICMAN.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'TOTALLENGTH' AND TABLE_NAME ='SEARCHJOB' AND TABLE_SCHEMA = 'DBO')
BEGIN
 ALTER TABLE [MUSICMAN].[DBO].[SEARCHJOB] ADD [TOTALLENGTH] bigint NOT NULL DEFAULT 0
END
GO

-- ALTER TABLE SEARCHFILE
IF NOT EXISTS(SELECT * FROM MUSICMAN.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'SEARCHSEQUENCE' AND TABLE_NAME ='SEARCHFILE' AND TABLE_SCHEMA = 'DBO')
BEGIN
 ALTER TABLE [MUSICMAN].[DBO].[SEARCHFILE] ADD [SEARCHSEQUENCE] bigint NOT NULL DEFAULT 0
END
GO
